package com.kandinfo.isbnmanage.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.kandinfo.base.common.repository.BaseRepository;
import com.kandinfo.isbnmanage.domian.HomeworkTemporary;

public interface HomeworkTemporaryRepository extends BaseRepository<HomeworkTemporary, Long>{

	@Query(value="select * from homework_temporary where homework_id = ?1 and is_cover = ?2 GROUP BY file_name  " ,nativeQuery=true)
	List<HomeworkTemporary> findByIsbnInformationId(Long isbnInformationId, String isCover);
	
	@Query(value="select * from homework_temporary where barcode = ?1 GROUP BY file_name  " ,nativeQuery=true)
	List<HomeworkTemporary> findByBarcode(String barcode);
	
	@Query(value="select * from homework_temporary where barcode = ?1 and homework_id = ?2 and is_cover = ?3 GROUP BY file_name  " ,nativeQuery=true)
	List<HomeworkTemporary> find(String barcode, Long isbnInformationId, String isCover);
	
	@Query(value="select * from homework_temporary where barcode = ?1 and homework_id = ?2 and file_name = ?3 order by id desc limit 1" ,nativeQuery=true)
	HomeworkTemporary get(String barcode, Long isbnInformationId, String fileName);
	
	@Query( value = "select * from homework_temporary ht where ht.status = '0' and ht.homework_id = ?1" , nativeQuery = true )
	List<HomeworkTemporary> findAllUntreatedHomework(Long homeworkId);

	@Query( value = "select * from homework_temporary ht where ht.status = ?2 and ht.homework_id = ?1 group by ht.file_name " , nativeQuery = true )
	List<HomeworkTemporary> findByIsbnInfoIdAndStatus(Long isbnInformationId, String status);

	@Modifying
	@Query( value = "update homework_temporary ht set ht.has_answer = ?2 where ht.homework_id = ?1" , nativeQuery = true )
	void updateHasAnswerByIsbnInfoId(Long homeworkId, Integer hasAnswer);

	@Modifying
	@Query( value = "delete  from  homework_temporary where homework_id = ?1 and is_cover = '0' " , nativeQuery = true )
	void removeCoverByInfoId(Long homeworkId);

	@Modifying
	@Query( value = "delete  from  homework_temporary where homework_id = ?1 and file_name = ?2 and id != ?3" , nativeQuery = true )
	void deletePage(Long isbnInformationId, String fileName, Long id);

	@Query( value = "select * from homework_temporary ht where ht.homework_id = ?1 and is_cover = '0' order by ht.create_ts desc Limit 1 " , nativeQuery = true )
	HomeworkTemporary findCoverByHomeworkId(Long isbnInformationId);
	
}
